Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


How To Identify Badly Formed SQL Statements

Badly tuned SQL statements tend to access the database in a very inefficient way, causing unnecessary amounts of data to be scanned and transferred across the network. Badly tuned statements can cause a well-tuned server to expend large amounts of unnecessary processing power and I/O resources.

You can identify badly tuned SQL statements with the Oracle EXPLAIN PLAN command and SQL Trace facility, as described in Chapter 25, “Using EXPLAIN PLAN and SQL Trace.” Some of the attributes of a badly tuned SQL statement are listed here:

  Indexes are not used. If a query is not properly formed, you may bypass an index that could be used to reduce I/O and CPU processing.
  Hashing is bypassed. If a hashed cluster is improperly accessed, performance could be severely degraded.
  Unnecessary table scans are performed. If the SQL statement is improperly formed, you may be doing unnecessary table scans.
  Unnecessary amounts of data are returned. This is an undue burden not only on the network but on the application as well.

These attributes should alert you to the fact that the SQL statements are not optimally tuned for the task being done. If your SQL statement exhibits any of these characteristics, you should make some correction to the statement. The remaining chapters in Part IV of this book, “Tuning SQL,” explain how to correct these problems.

Transaction Processing

Before looking at specific SQL statements, this section analyzes how a transaction occurs in Oracle and then looks in more detail at how the SQL statement is actually parsed and the execution plan formed. Remember that a transaction is a logical group of work consisting of one or many SQL statements and ending with a commit or a rollback. In a typical transaction, the following steps are executed (see Figure 24.1):

A.  Application Connection
1.  The application processes the user input and creates a connection to the server through SQL*Net.
2.  The server picks up the connection request and creates a server process on behalf of the user.
The application-connection process occurs only when the application is signing on.
The application does not have to connect each time a statement is processed.
B.  Application Processing
1.  The user executes an SQL statement and commits the transaction. For example, the user changes the value of a row in a table.
2.  The server process takes this SQL statement and checks the shared pool to see whether there is a shared SQL area that has this identical SQL statement. If it finds an identical shared SQL area, the server process checks to see whether the user has access privileges to the data and uses the shared SQL area to process the request. If a shared SQL area is not found, a new shared SQL area is allocated, the statement is parsed, and it is finally executed.
3.  The server process retrieves the data from the SGA (if present) or retrieves it from the data file into the SGA.
4.  The server process modifies the data in the SGA. Remember that the server processes can only read from the data files.
5.  The LGWR process writes out the redo information. Not until this redo information has been written to the log is the statement considered committed. At some later time, the DBWR process writes the modified blocks to permanent storage.
6.  If the transaction is successful, a completion code is returned across the network to the client process. If a failure occurs, an error message is returned.
7.  Return to phase B, “Application Processing,” and submit more transactions until you are finished and want to exit the application.
The Application Processing phase is repeated indefinitely until the user is finished with this particular application and exits the application.


NOTE:  A transaction is not considered committed until the write to the redo log file has been completed. This arrangement ensures that a committed transaction is recoverable in the event of a system failure. When a transaction has been committed and the redo entry has been written, the transaction is considered finished.
C.  Application Termination
1.  The application logs off the RDBMS. This event signals Oracle that all the associated resources can be deallocated.
2.  The Oracle PMON process makes sure that the server process has been terminated.
3.  All resources are released. Any memory resources the application has allocated are released.


Figure 24.1  A flowchart showing how an application is processed.

While this process is occurring, the Oracle background processes are doing their jobs keeping the system running smoothly. Keep in mind that your application is being processed, hundreds of other users may be doing similar tasks. It is Oracle’s job to keep the system in a consistent state, managing contention and locking and performing at the necessary rate.

Even though your application may have modified some data in the database, that data may not yet be written to the data files. It may be some time later that the DBWR process writes those changes out to permanent storage.

With this overview of how the application is processed, you are ready to focus on what happens in step 2 of phase B: how the SQL statement is parsed and the execution plan is formed.

SQL Statement Processing

By understanding how Oracle processes SQL statements, you can have a better understanding of how to optimize these statements. The following sections look at the SQL statement parsing process and how an execution plan is formed. For each SQL statement that is executed, several steps occur (see Figure 24.2):

1.  A cursor is created.
2.  The statement is parsed, if it is not already in the shared pool.
3.  Any query in the statement is processed.
4.  Variables are bound.
5.  The statement is executed.
6.  If possible, the statement is parallelized.
7.  Rows to be returned are fetched.


Figure 24.2  A flowchart showing how an SQL statement is processed.

Cursor Creation

Each time an SQL statement is executed, a cursor is automatically created on behalf of the statement. If you want, you can declare the cursor manually. Remember that a cursor is a handle to a specific private SQL area. You can think of a cursor as a pointer to, or the name of, a particular area of memory associated with an SQL statement.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.